if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetCOIQuestionnaireAnswers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetCOIQuestionnaireAnswers]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO   
/******************************************************************  
* Name:   GetCOIQuestionnaireAnswers  
* Purpose:  Retrieves a list of QuestionnaireAnswer based on the COI's  
*     id passed in as a parameter.  
*  
* PARAMETERS  
* Name      Description       
* -------------   -------------------------------------------  
* @coiId  The Id of the COI whose QuestionnaireAnswers are to be retrieved.  
******************************************************************/  
  
CREATE Procedure [dbo].[GetCOIQuestionnaireAnswers] (  
  @coiId  INTEGER   
)  
AS  
BEGIN  
  
 SET NOCOUNT ON  
  
 Select QA.Id,  
  QA.QuestionnaireVersionId,   
  QA.Name,  
  QA.Required,  
  QA.Editable,  
  Q.Category as 'QuestionnaireCategory',  
  QA.LastModifiedDate,  
  QA.ExternalId,  
  QA.IsClone,  
  QA.ActionTypeId,  
  QA.Highlight,
  QA.LastModifiedbyAdmPersonId,
  QA.Status,
  RC2.RefMeaning as StatusMeaning,
  Adp.LastName, +', '+Adp.FirstName  as LastModifiedBy,
  RCA.Value  
 FROM QuestionnaireAnswer QA  
  INNER JOIN QuestionnaireVersion QV   
   on QA.QuestionnaireVersionId = QV.ID  
  INNER JOIN Questionnaire Q  
   on QV.QuestionnaireId = Q.Id  
  INNER JOIN COI_QuestionnaireAnswer_Map map  
   on (map.QuestionnaireAnswerId = QA.Id and map.COIId = @coiId)
  Left Join AdmPerson adp
	on adp.Id = QA.LastModifiedByAdmPersonId  
  INNER JOIN refcode RC   
   ON Q.Category = RC.RefCode  
  INNER JOIN refcodeattribute RCA  
   on (rc.refdomain = rca.refdomain and rc.refcode = rca.refcode and rca.attribute = 'order')  
  LEFT JOIN refcode RC2 ON RC2.Refcode = QA.Status
 order by rca.[value], QA.Id ASC  
   
END  
  